from pg_sigleton import PostgerSingleton
import json
import logging
from datetime import datetime, timedelta
import requests
from requests.exceptions import Timeout
from setting import HEARTBEAT_INTERVAL
from util import send_mqtt_message
from setting import *

logging.basicConfig(level = logging.INFO,format = '%(asctime)s - %(name)s - %(levelname)s - %(message)s')

logger = logging.getLogger(__name__)

class DataBaseMethod:


    def __init__(self) -> None:
        pass

    def get_all_cabinet(self, page=1, per_page=10, sort='asc'): # 查询柜子

        """查询柜子"""

        offset = (page - 1) * per_page

        sort_order = 'ASC' if sort == 'asc' else 'DESC'


        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            query = f"SELECT * FROM cabinet ORDER BY id {sort_order} LIMIT {per_page} OFFSET {offset}"

            cur.execute(query)  # 执行 SQL 查询
            rows = cur.fetchall()  # 检索查询结果

            return rows
        
    def get_all_esp_tags(self, page=1, per_page=10, sort='asc'): # 查询esp标签

        """查询esp标签"""
        offset = (page - 1) * per_page

        sort_order = 'ASC' if sort == 'asc' else 'DESC'
        
        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            query = "SELECT * FROM esp_tags ORDER BY id {} LIMIT %s OFFSET %s".format(sort)

            parameters = (per_page, offset)
            
            cur.execute(query, parameters)  # 执行带参数的查询语句
            rows = cur.fetchall()  # 检索查询结果
            
            return rows
        
    def insert_esp_tags(self, tags):
        """批量插入esp标签"""
        
        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            query = "INSERT INTO esp_tags (tag_value) SELECT %s WHERE NOT EXISTS (SELECT 1 FROM esp_tags WHERE tag_value = %s)"
            
            unique_tags = set(tags)  # 去重，确保不重复插入已存在的标签
            
            cur.executemany(query, [(tag, tag) for tag in unique_tags])
                              

    def get_cabinet_info_by_mac(self, mac):

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("select * from cabinet where mac=%s", (mac,))  # 执行 SQL 查询
            rows = cur.fetchone()  # 检索查询结果

            return rows
        
    def get_cabinet_info_by_id(self, cabinet_id):

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("select * from cabinet where id=%s", (cabinet_id,))  # 执行 SQL 查询
            rows = cur.fetchone()  # 检索查询结果

            return rows
        
    def get_door_task_by_mac(self, mac):

        cabinet_info = self.get_cabinet_info_by_mac(mac)

        if not cabinet_info: # 如果mac没有找到返回None

            return None
        
        cabinet_id = cabinet_info["id"]

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("select * from door_task where cabinet_id=%s and task_status='进行中' order by id desc limit 1", (cabinet_id,))  # 执行 SQL 查询
            rows = cur.fetchone()  # 检索查询结果

            return rows
        
    def check_cabinet_current_status(self, data): 

        '''检查心跳交来的设备状态和数据库状态是否同步
        data = {"mac": mac, "cabinet_current_status": "盘点结束"}'''

        mac = data["mac"]

        cabinet_current_status = data['cabinet_current_status']

        cabinet_info = self.get_cabinet_info_by_mac(mac)

        if not cabinet_info:

            logger.error("{}: 没有找到".format(mac))

            return None

        cabinet_id = cabinet_info.get("id")

        db_status = self.get_last_door_task_status(cabinet_id)


        logger.info('db_status != "结束" and cabinet_current_status == "盘点结束": {}'.format(db_status != "结束" and cabinet_current_status == "盘点结束"))

        if db_status != "结束" and cabinet_current_status == "盘点结束":

            # 通知设备重新提交最新盘点结果

            topic = "rfid_cabinet/rx/{}".format(mac)

            message = {

                "task_type": "提交盘点信息",
            }

            # 状态不同一，同步盘点信息
            send_mqtt_message(broker_address=BROKER_ADDRESS, broker_port=BROKER_PORT, topic=topic, message=json.dumps(message))

        # 处理下极端情况

        cabinet_status_table_value = self.get_cabinet_status_by_mac(mac=mac) 

        if cabinet_status_table_value == None:

            cabinet_status_table_value = None

        else:

            cabinet_status_table_value = cabinet_status_table_value["current_status"]


        # 处理下极端情况
        if cabinet_current_status == "盘点结束" and db_status == "结束" and cabinet_status_table_value != "盘点结束":

            self.insert_cabinet_status(data={"mac": mac, "status": "盘点结束"})
            


    def insert_cabinet_heartbeat(self, data):
        '''心跳插入
        data: {"mac": mac, "cabinet_current_status": "盘点结束"}
        '''

        cabinet_info = self.get_cabinet_info_by_mac(data["mac"])

        if not cabinet_info: # 如果mac没有找到返回None

            return None
        
        cabinet_id = cabinet_info["id"]

        with PostgerSingleton() as pg_singleton:

            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("insert into cabinet_heartbeat(cabinet_id) values (%s)", (cabinet_id, ))  # 执行 SQL

    
    def insert_cabinet_status(self, data):
        '''设备状态插入
        data: {"mac": mac, "status": "盘点结束"}
        '''

        cabinet_info = self.get_cabinet_info_by_mac(data["mac"])

        if not cabinet_info: # 如果mac没有找到返回None

            return None
        
        cabinet_id = cabinet_info["id"]

        with PostgerSingleton() as pg_singleton:

            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("insert into cabinet_status(cabinet_id, current_status) values (%s, %s)", (cabinet_id, data["status"]))  # 执行 SQL 查询


    def insert_door_task(self, data: dict):
        '''插入开门任务

        data: {"orderid": "xxxxxx", "mac": mac}
        '''

        cabinet_info = self.get_cabinet_info_by_mac(data["mac"])

        if not cabinet_info: # 如果mac没有找到返回None

            return None
        
        cabinet_id = cabinet_info["id"]

        with PostgerSingleton() as pg_singleton:

            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("insert into door_task(cabinet_id, orderid) values (%s, %s)", (cabinet_id, data["orderid"]))  # 执行 SQL 查询

    def insert_door_task_by_cabinet_id(self, data: dict):
        '''插入开门任务

        data: {"orderid": "xxxxxx", "cabinet_id": cabinet_id}
        '''

        cabinet_info = self.get_cabinet_info_by_id(data["cabinet_id"])

        if not cabinet_info: # 如果mac没有找到返回None

            return None
        
        cabinet_id = cabinet_info["id"]

        with PostgerSingleton() as pg_singleton:

            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            sql = "select * from door_task where orderid=%s"

            cur.execute(sql, (data["orderid"],))

            if not cur.fetchone():

                cur.execute("insert into door_task(cabinet_id, orderid) values (%s, %s)", (cabinet_id, data["orderid"]))  # 执行 SQL 查询


    def update_door_task_close_time(self, data):
        '''升级door_task任务： 升级关门时间
        data: {"mac": mac}
        '''

        cabinet_info = self.get_door_task_by_mac(data["mac"])

        logger.info(cabinet_info)

        if not cabinet_info: # 如果mac没有找到返回None

            return None
        
        task_id = cabinet_info["id"]

        with PostgerSingleton() as pg_singleton:

            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("UPDATE door_task SET close_time = CURRENT_TIMESTAMP WHERE id = %s", (task_id,))# 升级关门时间
    
    
    def get_last_success_door_task_by_cabinet_id(self, cabinet_id, task_id): # 获取最近完成的door_task任务

        with PostgerSingleton() as pg_singleton:

            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            # # 最近一条任务
            # last_door_task_sql = "select * from door_task where task_status='结束' and cabinet_id=%s order by id desc limit 1"

            # 查询上条数据
            previous_data_sql = '''\
                SELECT * FROM door_task
                WHERE cabinet_id = %s
                AND id < %s
                ORDER BY id DESC
                LIMIT 1;
            '''

            cur.execute(previous_data_sql, (cabinet_id, task_id))  # 执行 SQL 查询

            last_door_task_data = cur.fetchone()  # 检索查询结果

        if not last_door_task_data: # 上一条数据没有，证明是第一次提交esp标签数据

            return None
        
        return last_door_task_data
    

    def settlement_callback_function(self, door_task_id, orderid, settlement): # 向结算链接发送同步数据

        '''向结算链接发送同步数据'''


        settlement_callback_info = self.get_settlement_callback_info()

        if not settlement_callback_info:

            self.insert_callback_result_for_door_task(door_task_id, "回调连接未定义")

            return 
        
        callback_url = settlement_callback_info["callback_url"]

        data = {

            "door_task_id": door_task_id,

            "orderid": orderid,

            "settlement": settlement

        }

        try:

            # response = requests.post(callback_url, data=data, timeout=1)

            raw_data = json.dumps(data)  # 将数据转换为 JSON 字符串
            headers = {'Content-Type': 'application/json'}  # 设置请求头为 JSON 格式
            response = requests.post(callback_url, data=raw_data, headers=headers, timeout=1)

        # 处理响应
        except Timeout as e:
            # 请求超时，处理其他业务逻辑

            logger.error("settlement_callback_function: 超时")

            
            self.insert_callback_result_for_door_task(door_task_id, "失败")

            self.update_door_task_sync_status(door_task_id, "失败")


        except Exception as e:

            logger.error("settlement_callback_function: {}".format(str(e)))

        else:

            result = response.text # 返回 true false

            if result == "true":

                self.insert_callback_result_for_door_task(door_task_id, "成功")
                self.update_door_task_sync_status(door_task_id, "成功")


            else:

                logger.error("settlement_callback_function 错误返回信息: {}".format(result))

                self.insert_callback_result_for_door_task(door_task_id, "失败")
                self.update_door_task_sync_status(door_task_id, "失败")




    def update_door_task_inventory_data(self, data):
        '''升级door_task任务： 升级关门时间
        data: {"mac": mac, "esp_list": now_esp_list}
        '''

        door_task_info = self.get_door_task_by_mac(data["mac"])

        if not door_task_info: # 如果mac没有找到返回None

            return None
        
        task_id = door_task_info["id"]

        cabinet_id = door_task_info["cabinet_id"]

        orderid = door_task_info["orderid"]

        self.get_esp_list_by_cabinet_id(cabinet_id)
        
        last_door_task_info = self.get_last_success_door_task_by_cabinet_id(cabinet_id=cabinet_id, task_id=task_id)

        if not last_door_task_info: # 第一次上交

            previous_esp_list = []

        else:

            previous_esp_list = last_door_task_info.get("inventory_data", [])

            if previous_esp_list == None:

                previous_esp_list = [] #TODO 这里要大改

        settlement_list = self.compare_inventory(previous_esp_list=previous_esp_list, now_esp_list=data["esp_list"])

        if orderid.startswith("bh") and settlement_list["deleted_esp"]: # 如果补货订单，把取出来的标识为库存

            self.update_esp_tag_status(tag_values=settlement_list["deleted_esp"], status="库存")

        with PostgerSingleton() as pg_singleton:

            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("UPDATE door_task SET json_generated_time = CURRENT_TIMESTAMP, settlement=%s,\
                        submit_time = CURRENT_TIMESTAMP, task_status='结束',sync_status='等待同步', inventory_data =%s WHERE id = %s",\
                        (json.dumps(settlement_list), json.dumps(data["esp_list"]), task_id))# 升级esp list
            
        self.settlement_callback_function(door_task_id = task_id, orderid = orderid, settlement=settlement_list ) # 将数据回调给SAAS平台
    
    def get_door_task_by_sync_status_not_success(self)->list: # 获取同步状态没有不成功的 door_task任务
        '''获取同步状态没有不成功的 door_task任务'''

        with PostgerSingleton() as pg_singleton:

            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            sql = "select * from door_task where sync_status='等待同步' or sync_status='失败'"

            cur.execute(sql)  # 执行 SQL 查询

            result = cur.fetchall()

        return result
    
    def update_door_task_sync_status(self, door_task_id: int, sync_status: str) -> None:
        '''更新 sync_status 状态'''

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            sql = "UPDATE door_task SET sync_status = %s WHERE id = %s"

            cur.execute(sql, (sync_status, door_task_id))  # 执行 SQL 更新
    
    
    def get_cabinet_heartbeat_by_id(self, cabinet_id):

        '''获取柜子当前最后一次心跳'''

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("select * from cabinet_heartbeat where cabinet_id=%s order by id desc limit 1", (cabinet_id,))  # 执行 SQL 查询
            
            rows = cur.fetchone()  # 检索查询结果

            return rows

    def get_cabinet_status_by_id(self, cabinet_id):
        '''获取柜子当前状态'''

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("select * from cabinet_status where cabinet_id=%s order by id desc limit 1", (cabinet_id,))  # 执行 SQL 查询
            rows = cur.fetchone()  # 检索查询结果

            return rows
    
    def get_cabinet_status_by_mac(self, mac):
        '''获取柜子当前状态'''

        cabinet_info = self.get_cabinet_info_by_mac(mac)

        if not cabinet_info: # 如果mac没有找到返回None

            return None
        
        cabinet_id = cabinet_info["id"]

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("select * from cabinet_status where cabinet_id=%s order by id desc limit 1", (cabinet_id,))  # 执行 SQL 查询
            rows = cur.fetchone()  # 检索查询结果

            return rows
        
    def get_esp_list_by_cabinet_id(self, cabinet_id):

        """获取esp列表，返回的是一个列表"""

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("select * from door_task where cabinet_id=%s order by id desc limit 1", (cabinet_id,))  # 执行 SQL 查询
            rows = cur.fetchone()  # 检索查询结果

        if not rows:

            return None
        
        return rows["inventory_data"]
    
    def get_esp_list_by_cabinet_mac(self, mac):

        """获取esp列表，返回的是一个列表"""

        cabinet_info = self.get_cabinet_info_by_mac(mac)

        if cabinet_info == None:

            return []
        
        cabinet_id = cabinet_info['id']

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("select * from door_task where cabinet_id=%s order by id desc limit 1", (cabinet_id,))  # 执行 SQL 查询
            rows = cur.fetchone()  # 检索查询结果

        if not rows:

            return None
        
        return rows["inventory_data"]
    
    
        
    def filter_sold_tags(self, tag_values): # 过滤出已售标签
        """筛选出状态为已售"""
        
        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            query = "SELECT tag_value FROM esp_tags WHERE tags_status = '已售' AND tag_value in %s"
            
            cur.execute(query, (tuple(tag_values),))  # 执行查询语句

            rows = cur.fetchall()  # 检索查询结果

            # 将查询结果转换为标签值的列表
            filtered_tags = [row["tag_value"] for row in rows]
            
            return filtered_tags
        
    def filter_not_recorded_tags(self, tag_values):
        """筛选出不存在于记录中的标签"""
        
        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            query = "SELECT tag_value FROM esp_tags"
            
            cur.execute(query)  # 执行查询语句
            rows = cur.fetchall()  # 检索查询结果
            
            # 将数据库中存在的标签值转换为集合
            db_tags = set(row["tag_value"] for row in rows)
            
            # 筛选出不存在于记录中的标签
            filtered_tags = [tag for tag in tag_values if tag not in db_tags]
            
            return filtered_tags
        
    def get_non_stock_esp_tags(self, tag_values): # 筛选异常标签 TODO可优化
        """获取异常esp标签"""
        
        return self.filter_sold_tags(tag_values) + self.filter_not_recorded_tags(tag_values)
        
    def update_esp_tag_status(self, tag_values, status):
        """将标签的状态升级为最新状态"""
        
        with PostgerSingleton() as pg_singleton:

            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            query = "UPDATE esp_tags SET tags_status = %s WHERE tag_value = ANY(%s)"
            
            cur.execute(query, (status, tag_values))  # 执行带参数的更新语句


    def get_settlement_callback_info(self):
        '''获取最新的SAAS平台回调路由信息'''

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("select * from settlement_callback  order by id desc limit 1")  # 执行 SQL 查询

            rows = cur.fetchone()  # 检索查询结果

            return rows
        
    
    def insert_settlement_callback_route_info(self, callback_url):
        '''插入SAAS平台回调路由信息'''
        
        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            sql = "INSERT INTO settlement_callback (callback_url) VALUES (%s)"
            values = (callback_url, )
            
            cur.execute(sql, values)  # 执行 SQL 插入操作

        
    def insert_callback_result_for_door_task(self, task_id, sync_status):
        '''向callback_result_for_door_task表插入数据'''
        
        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            sql = "INSERT INTO callback_result_for_door_task (task_id, sync_status) VALUES (%s, %s)"
            values = (task_id, sync_status)
            
            cur.execute(sql, values)  # 执行 SQL 插入操作
            conn.commit()  # 提交事务
            
            return cur.rowcount  # 返回受影响的行数
    
    def get_latest_callback_result_by_orderid(self, orderid):
        '''根据orderid查询callback_result_for_door_task表，获取最新的一条数据'''
        
        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            sql = "SELECT * FROM callback_result_for_door_task WHERE task_id IN (SELECT id FROM door_task WHERE orderid = %s) ORDER BY id DESC LIMIT 1"
            values = (orderid,)
            
            cur.execute(sql, values)  # 执行 SQL 查询操作
            rows = cur.fetchone()  # 检索查询结果
            
            return rows
        
    def get_latest_callback_result_by_task_id(self, task_id):
        '''根据task_id查询callback_result_for_door_task表，获取最新的一条数据'''
        
        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            sql = "SELECT * FROM callback_result_for_door_task WHERE task_id=%s ORDER BY id DESC LIMIT 1"
            values = (task_id,)
            
            cur.execute(sql, values)  # 执行 SQL 查询操作
            rows = cur.fetchone()  # 检索查询结果
            
            return rows
        
    def get_all_callback_result_by_task_id(self, task_id):
        '''根据task_id查询callback_result_for_door_task表，获取所有数据'''
        
        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            sql = "SELECT * FROM callback_result_for_door_task WHERE task_id=%s"
            values = (task_id,)
            
            cur.execute(sql, values)  # 执行 SQL 查询操作
            rows = cur.fetchall()  # 检索查询结果
            
            return rows
        
    
    def get_failed_door_task_id(self) -> list:
        '''获取回调失败的door_task的ID列表'''

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            sql = "SELECT task_id FROM callback_result_for_door_task WHERE sync_status = '失败'"
            
            cur.execute(sql)  # 执行 SQL 查询操作
            rows = cur.fetchall()  # 检索查询结果

            failed_task_ids = [row["task_id"] for row in rows]  # 提取task_id字段值

            return failed_task_ids

    def get_id_list_by_mac_list(self, mac_list) -> dict: 
        '''获取mac对应的cabinet_id'''

        result = {}

        with PostgerSingleton() as pg_singleton:

            conn, cur = pg_singleton

            sql = "SELECT id FROM cabinet WHERE mac = %s;"

            for mac in mac_list:

                cur.execute(sql, (mac,))

                results = cur.fetchone()

                if results:

                    result[mac] = results['id']

        return result
    
    # def get_online_status_by_mac_list(self, cabinet_id_list):

    def online_judgment(self, heartbeat_time):
        '''在线判断'''

        current_time = datetime.now()

        time_difference = current_time - heartbeat_time

        if time_difference > timedelta(seconds=HEARTBEAT_INTERVAL*2): # 心跳过期
            
            return False
        
        return True


    def get_online_status_by_id_list(self, cabinet_id_list):
        '''获取在线状态'''
        online_status = {}

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton

            # Generate a comma-separated string of cabinet_id values
            cabinet_ids = ', '.join(str(id) for id in cabinet_id_list)

            # Create the SQL query to select the last heartbeat data for each cabinet_id
            sql = f'''
                SELECT DISTINCT ON (cabinet_id) cabinet_id, heartbeat_time
                FROM cabinet_heartbeat
                WHERE cabinet_id IN ({cabinet_ids})
                ORDER BY cabinet_id, heartbeat_time DESC;
            '''

            cur.execute(sql)
            results = cur.fetchall()

            for i in results:

                online_status[i["cabinet_id"]] = {"online_status": self.online_judgment(i["heartbeat_time"]), "last_heartbeat_time": i["heartbeat_time"]}

            return online_status
        
    
    def get_online_status_by_mac_list(self, cabinet_mac_list):

        result = {}

        mac_dict = self.get_id_list_by_mac_list(cabinet_mac_list)

        cabinet_id_list = list(mac_dict.values())

        online_status = self.get_online_status_by_id_list(cabinet_id_list)

        for mac, cabinet_id in mac_dict.items():

            result[mac] = online_status.get(cabinet_id)

        return result


    def get_latest_status_by_id_list(self, cabinet_id_list):

        current_status = {}
        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton

            # Generate a comma-separated string of cabinet_id values
            cabinet_ids = ', '.join(str(id) for id in cabinet_id_list)

            # Create the SQL query to select the latest status for each cabinet_id
            sql = f'''
                SELECT DISTINCT ON (cabinet_id) cabinet_id, current_status
                FROM cabinet_status
                WHERE cabinet_id IN ({cabinet_ids})
                ORDER BY cabinet_id, status_time DESC;
            '''

            cur.execute(sql)

            results = cur.fetchall()

            for i in results:

                current_status[i["cabinet_id"]] = i["current_status"]

            return current_status

    def get_latest_status_by_mac_list(self, cabinet_mac_list):

        result = {}

        mac_dict = self.get_id_list_by_mac_list(cabinet_mac_list)

        cabinet_id_list = list(mac_dict.values())

        status = self.get_latest_status_by_id_list(cabinet_id_list)

        for mac, cabinet_id in mac_dict.items():

            result[mac] = status.get(cabinet_id)

        return result
    
    def get_last_door_task_status(self, cabinet_id):

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton

            sql = "select * from door_task where cabinet_id = %s order by open_time desc limit 1"
            cur.execute(sql, (cabinet_id,))
            result = cur.fetchone()

            if result:
                return result.get("task_status")
            
            else:

                return "结束"
            
    def test_update_esp_tag_status(self): # 测试用函数

        with PostgerSingleton() as pg_singleton:

            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            query = "UPDATE esp_tags SET tags_status = '库存'"
            
            cur.execute(query)  # 执行带参数的更新语句

    def test_update_esp_tag_status_by_esp_list(self, esp_list): # 测试用函数

        with PostgerSingleton() as pg_singleton:

            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            query = "UPDATE esp_tags SET tags_status = '库存' where tag_value in %s"
            
            cur.execute(query, (tuple(esp_list),))  # 执行带参数的更新语句

    def add_advertisement(self, cabinet_id, ad_url):
        '''设置广告'''

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            query = "INSERT INTO ad (cabinet_id, ad_url) VALUES (%s, %s)"
            
            cur.execute(query, (cabinet_id, ad_url))  # 执行带参数的插入语句

    def upgrade_ad_sync_status(self, ad_id, new_status):
        '''升级广告配置状态'''
        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            query = "UPDATE ad SET sync_status = %s WHERE id = %s"
            
            cur.execute(query, (new_status, ad_id))  # 执行带参数的更新语句



    def insert_cabinet_abnormal(self, data):
        '''数据插入cabinet_abnormal

        data : {
            'mac': '7ad7555fe25f', 
            'cabinet_current_status': '盘点结束', 
            'cabinet_abnormal': {'rssi_4g': 28, 'cabinet_temp': 6.1, 'rssi_4g_status': '正常', 'cabinet_temp_status': '正常'}
        }
        
        '''

        cabinet_info = self.get_cabinet_info_by_mac(data["mac"])

        if not cabinet_info:

            return
        
        cabinet_id = cabinet_info["id"]

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            sql = "INSERT INTO cabinet_abnormal(cabinet_id, abnormal_json) values (%s, %s)"
            
            cur.execute(sql, (cabinet_id, json.dumps(data.get("cabinet_abnormal"))))  # 执行带参数的更新语句

    def get_cabinet_abnormal_by_mac_list(self, cabinet_mac_list):

        result = {}

        mac_dict = self.get_id_list_by_mac_list(cabinet_mac_list)

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            sql = "select * from cabinet_abnormal where cabinet_id=%s order by id desc limit 1"

            for mac, cabinet_id in mac_dict.items():
            
                cur.execute(sql, (cabinet_id, ))  # 执行带参数的更新语句

                row = cur.fetchone()

                result[mac] = row

        return result
    

    def get_cabinet_mac_by_orderid(self, orderid):
        '''输入orderid 找到 cabinet mac'''

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            sql = "select (cabinet_id) from door_task where orderid=%s"

            cur.execute(sql, (orderid,))

            info = cur.fetchone()

            if not info:

                return "订单未找到对应的购物柜"
            
            cabinet_id = info['cabinet_id']

            sql = "select (mac) from cabinet where id=%s"

            cur.execute(sql, (cabinet_id,))

            return cur.fetchone()["mac"]

    def insert_upload_video_status(self, data):
        '''insert_upload_video_status

        data : {
            'orderid': '7ad7555fe25f', 
            'md5': '8bc3bd3e3179db670032fe721ecddc60', 
        }
        
        '''

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            sql = """INSERT INTO upload_video_status(orderid, md5)
                        VALUES (%s, %s)
                        ON CONFLICT (orderid) 
                        DO UPDATE SET md5 = EXCLUDED.md5;
                        
            """
            
            cur.execute(sql, (data["orderid"], data["md5"]))  # 执行带参数的更新语句

    def update_upload_video_status(self, data):
        '''升级 upload_video_status 状态

        data : {
            'orderid': '7ad7555fe25f.mp4', 
            'calculate_md5_status': '验证成功',  -- '验证失败'
        }
        
        '''

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            sql = """UPDATE upload_video_status SET calculate_md5_status=%s WHERE orderid=%s"""
            
            cur.execute(sql, (data["calculate_md5_status"], data["orderid"]))  # 执行带参数的更新语句

    def get_upload_video_status_by_calculate_md5_status(self, status='待验证'):
        '''查找 待验证 状态的数据 '''

        with PostgerSingleton() as pg_singleton:

            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            sql = """SELECT * FROM  upload_video_status WHERE calculate_md5_status=%s;"""
            
            cur.execute(sql, (status, ))  # 执行带参数的更新语句

            info = cur.fetchone()

        return info
    
    def get_last_door_task_by_video_status_is_null(self): # 获取door_task视频为上传的任务

        with PostgerSingleton() as pg_singleton:

            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            sql = """SELECT * FROM  door_task WHERE video_status is null and task_status='结束' ORDER BY ID DESC limit 1;"""
            
            cur.execute(sql)  # 执行带参数的更新语句

            info = cur.fetchone()

        return info
    
    def update_door_task_video_status(self, data):
        '''升级 upload_video_status 状态

        data : {
            'orderid': '7ad7555fe25f', 
            'video_status': 'md5验证成功', "上传任务以下发、md5验证成功、md5验证失败、"
        }
        
        '''

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象
            
            sql = """UPDATE door_task SET video_status=%s WHERE orderid=%s"""
            
            cur.execute(sql, (data["video_status"], data["orderid"]))  # 执行带参数的更新语句













    ########## 封装区##########

    def get_cabinet_status(self, cabinet_id): # 获取设备当前状态

        last_cabinet_heartbeat = self.get_cabinet_heartbeat_by_id(cabinet_id) # 最近一次心跳

        # {'id': 1215, 'cabinet_id': 1, 'heartbeat_time': datetime(2023, 12, 6, 15, 47, 16, 216111)}

        # print (last_cabinet_heartbeat)

        if not last_cabinet_heartbeat: # 没交过心跳

            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': '离线',
            }
        
        current_time = datetime.now()

        heartbeat_time = last_cabinet_heartbeat.get('heartbeat_time')

        time_difference = current_time - heartbeat_time

        if time_difference > timedelta(seconds=HEARTBEAT_INTERVAL*2): # 心跳过期
            
            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': '离线',
            }
        
        cabinet_status = self.get_cabinet_status_by_id(cabinet_id)

        if not cabinet_status:

            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': "盘点结束",  # TODO
            }

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': cabinet_status["current_status"],
        }
    

    def get_cabinet_status_by_mac_api(self, mac): # 获取设备当前状态

        cabinet_info = self.get_cabinet_info_by_mac(mac)

        if cabinet_info == None:

            return {
                'status': -1,
                'errorCode': 4004,
                'errorMsg': "没有找到这个设备",
                'resultBody': None,
            }
        
        cabinet_id = cabinet_info["id"]

        last_cabinet_heartbeat = self.get_cabinet_heartbeat_by_id(cabinet_id) # 最近一次心跳

        # {'id': 1215, 'cabinet_id': 1, 'heartbeat_time': datetime(2023, 12, 6, 15, 47, 16, 216111)}

        # print (last_cabinet_heartbeat)

        if not last_cabinet_heartbeat: # 没交过心跳

            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': '离线',
            }
        
        current_time = datetime.now()

        heartbeat_time = last_cabinet_heartbeat.get('heartbeat_time')

        time_difference = current_time - heartbeat_time

        if time_difference > timedelta(seconds=HEARTBEAT_INTERVAL*2): # 心跳过期
            
            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': '离线',
            }
        
        cabinet_status = self.get_cabinet_status_by_id(cabinet_id)

        if not cabinet_status:

            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': "盘点结束",  # TODO
            }

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': cabinet_status["current_status"],
        }
        
    
    
    def compare_inventory_(self, previous_esp_list, now_esp_list): 

        '''当前列表和之前列表对比'''

        added_esp = []  # 添加的标签列表
        deleted_esp = []  # 删除的标签列表

        # 检查每个标签是否在当前盘点数据中
        for esp in previous_esp_list:
            if esp not in now_esp_list:
                deleted_esp.append(esp)

        # 检查每个标签是否在前一次盘点数据中
        for esp in now_esp_list:
            if esp not in previous_esp_list:
                added_esp.append(esp)

        result = {
            "added_esp": added_esp,
            "deleted_esp": deleted_esp
        }

        return result     

    def compare_inventory_(self, previous_esp_list, now_esp_list): 
        '''当前列表和之前列表对比

        # 这个函数的non_stock_esp 表示柜内所有的非法标签 和compare_inventory函数对比
        
        '''

        added_esp = []  # 添加的标签列表
        deleted_esp = []  # 删除的标签列表
        
        # 获取状态不为库存的esp标签
        non_stock_esp = self.get_non_stock_esp_tags(now_esp_list)

        # 检查每个标签是否在当前盘点数据中
        for esp in previous_esp_list:
            if esp not in now_esp_list and esp not in non_stock_esp:
                deleted_esp.append(esp)

        # 检查每个标签是否在前一次盘点数据中
        for esp in now_esp_list:
            if esp not in previous_esp_list and esp not in non_stock_esp:
                added_esp.append(esp)

        result = {
            "added_esp": added_esp,
            "deleted_esp": deleted_esp,
            "non_stock_esp" : non_stock_esp
        }

        self.update_esp_tag_status(added_esp, '柜内')
        self.update_esp_tag_status(deleted_esp, '已售')

        return result    
    def compare_inventory(self, previous_esp_list, now_esp_list):  
        '''当前列表和之前列表对比
        
        这个函数的non_stock_esp 表示有非法标签增量 compare_inventory函数对比'''

        added_esp = []  # 添加的标签列表
        deleted_esp = []  # 删除的标签列表
        
        # 获取状态不为库存的esp标签
        if now_esp_list == []:

            non_stock_esp_ = []

        else:
            non_stock_esp_ = self.get_non_stock_esp_tags(now_esp_list)

        # 检查每个标签是否在当前盘点数据中
        for esp in previous_esp_list:
            if esp not in now_esp_list:
                deleted_esp.append(esp)

        # 检查每个标签是否在前一次盘点数据中
        for esp in now_esp_list:
            if esp not in previous_esp_list:
                added_esp.append(esp)

        # 获取状态不为库存的esp标签
        if added_esp == []:

            non_stock_esp = []

        else:

            non_stock_esp = self.get_non_stock_esp_tags(added_esp)

        if deleted_esp == []:

            delete_non_stock_esp = []

        else:
        
            delete_non_stock_esp = self.get_non_stock_esp_tags(deleted_esp)


        result = {
            "added_esp": [esp for esp in added_esp if esp not in non_stock_esp],
            "deleted_esp": [esp for esp in deleted_esp if esp not in delete_non_stock_esp],
            "non_stock_esp" : non_stock_esp,
            "delete_non_stock_esp": delete_non_stock_esp,
            "non_stock_esp_": non_stock_esp_, # 当前柜内有的非法标签
        }

        self.update_esp_tag_status(result['added_esp'], '柜内')
        self.update_esp_tag_status(result['deleted_esp'], '已售')

        return result   

    def get_esp_difference_by_door_orderid(self, orderid):

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("select * from door_task where orderid=%s", (orderid,))  # 执行 SQL 查询
            rows = cur.fetchone()  # 检索查询结果

            if not rows: # 没有添加开门记录

                return {
                    'status': -1,
                    'errorCode': "2000",
                    'errorMsg': '请检查orderid',
                    'resultBody': None
                }
            if rows["task_status"] != '结束':

                return {
                    'status': -1,
                    'errorCode': "2001",
                    'errorMsg': '当前orderid还未结束',
                    'resultBody': None
                }

        return {
        'status': 1,
        'errorCode': None,
        'errorMsg': None,
        'resultBody': rows["settlement"],
        }


    
    def get_esp_difference_by_door_orderid_(self, orderid): # 逻辑不对方法遗弃

        """获取指定开门任务盘点esp列表和上次结束状态开门esp列表的差异
        result = {
            "added_esp": [], # 添加的标签列表
            "deleted_esp": [] # 删除的标签列表
            "non_stock_esp":[] # 异常标签
        }
        """

        with PostgerSingleton() as pg_singleton:
            conn, cur = pg_singleton  # 获取数据库连接和游标对象

            cur.execute("select * from door_task where orderid=%s", (orderid,))  # 执行 SQL 查询
            rows = cur.fetchone()  # 检索查询结果

            if not rows: # 没有添加开门记录

                return {
                    'status': -1,
                    'errorCode': "2000",
                    'errorMsg': '请检查orderid',
                    'resultBody': None
                }
            if rows["task_status"] != '结束':

                return {
                    'status': -1,
                    'errorCode': "2001",
                    'errorMsg': '当前orderid还未结束',
                    'resultBody': None
                }


            door_task_id = rows['id']

            cabinet_id = rows['cabinet_id']

            now_esp_list = rows["inventory_data"]

            # 查询上条数据
            previous_data_sql = '''\
                SELECT * FROM door_task
                WHERE cabinet_id = %s
                AND id < %s
                ORDER BY id DESC
                LIMIT 1;
            '''

            cur.execute(previous_data_sql, (cabinet_id, door_task_id))  # 执行 SQL 查询

            previous_data = cur.fetchone()  # 检索查询结果

        if not previous_data: # 上一条数据没有，证明是第一次提交esp标签数据

            return {
                'status': 1,
                'errorCode': None,
                'errorMsg': None,
                'resultBody': self.compare_inventory([], now_esp_list),
            }
        
        if previous_data["task_status"] != '结束':

            return {
                'status': -1,
                'errorCode': "2003",
                'errorMsg': '上一次开门任务盘点结果未提交',
                'resultBody': None
            }
        
        previous_esp_list = previous_data["inventory_data"] # 上次盘点esp数据

        return {
            'status': 1,
            'errorCode': None,
            'errorMsg': None,
            'resultBody': self.compare_inventory(previous_esp_list, now_esp_list),
        }
       
    




if __name__ == "__main__":

    dm = DataBaseMethod()

    dm.get_cabinet_info_by_mac("620790426a0e")



